# coding=utf-8

import os
import sys
import pyodbc
import time


DATABASE=sys.argv[1]

if DATABASE == 'GBOS':
    file_path = 'GBOS-BOSSQL-GBOS'
elif DATABASE == 'IFT':
    file_path = 'GBOS-BOSSQL-IFT'
elif DATABASE == 'CARE':
    file_path = 'GBOS-BOSCRMSQL-CARE'
elif DATABASE == 'CAS':
    file_path = 'GBOS-BOSCRMSQL-CAS'
elif DATABASE == 'VIP':
    file_path = 'GBOS-BOSCRMSQL-VIP'
elif DATABASE == 'IDEA':
    file_path = 'GBOS-BOSTOKENSQL-IDEA'
else:
    file_path = ''
    print('The parameter is not valid')
    exit(1)

print('get started...')
# Define the connection string to the Azure SQL database
conn_str = (
    "Driver={ODBC Driver 17 for SQL Server};"
    "Server=dv-bos-eus-gbos-001-sql.database.windows.net;"
    f"Database={DATABASE};"
    "UID=vincent.tang@greendotcorp.com;"
    "PWD=xxx;"
    "Authentication=ActiveDirectoryInteractive;"
)
'''
To connect to your Azure SQL database using Python with Multi-Factor Authentication (MFA), you can use the ActiveDirectoryInteractive authentication method. This method will prompt you to authenticate interactively, similar to how you do it in SQL Server Management Studio (SSMS)
'''
# Connect to the Azure SQL database
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Retrieve the deployment sequence from the control table
cursor.execute("SELECT SequenceID, TableName FROM dbo.TableDeploymentSequenceControl ORDER BY SequenceID asc")
deployment_sequence = cursor.fetchall()

print('cursor.fetchall..')

folder_path = rf"C:\Users\vincent.tang.NEXTESTATE\OneDrive - GREEN DOT CORPORATION\Desktop\0924 TimeZoneConversion-part1\{file_path}"
merge_path = rf"C:\Users\vincent.tang.NEXTESTATE\OneDrive - GREEN DOT CORPORATION\Desktop\0924 TimeZoneConversion-part1\MergeCode\{file_path}"

# Get system date
date = time.strftime('%Y%m%d',time.localtime())

######### Merge Table
merge_table_file_path = os.path.join(merge_path, rf"{DATABASE}_initial_table_{date}.sql")
with open(merge_table_file_path, 'w', encoding="utf8") as merge_table_file:
    beginning = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + '\n' + 'BEGIN TRAN' + '\n' + 'SET QUOTED_IDENTIFIER ON' + '\n' + 'GO' + '\n'
    merge_table_file.write(beginning)
    merge_table_file.close()

# Deploy each table creation script based on the sequence
for row in deployment_sequence:
    table_name = row.TableName
    script_file_path = os.path.join(folder_path, rf"Tables\{table_name}.sql")

    # Check if the script file exists
    if os.path.exists(script_file_path):
        # Read the content of the script file
        print('script_file_path: ',script_file_path)
        with open(script_file_path, 'r', encoding="utf8") as file:
            script_content = file.read()

        with open(merge_table_file_path, 'a', encoding="utf8") as merge_table_file:
            # merge_content = merge_table_file.read()
            # merge_content = merge_content + '\n' + script_content
            merge_content = '\n' + script_content
            merge_table_file.write(merge_content)
            merge_table_file.close()

with open(merge_table_file_path, 'a', encoding="utf8") as merge_table_file:
    #merge_content = merge_table_file.read()
    ending = '\n' + 'COMMIT TRAN' + '\n'
    #merge_content = merge_content + ending
    merge_table_file.write(ending)
    merge_table_file.close()


######### Merge Type
merge_type_file_path = os.path.join(merge_path, rf"{DATABASE}_initial_type_{date}.sql")
folder_path_type = os.path.join(folder_path, r'Types\User-defined Data Types')

with open(merge_type_file_path, 'w', encoding="utf8") as merge_table_file:
    beginning = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + '\n' + 'BEGIN TRAN' + '\n'
    merge_table_file.write(beginning)
    merge_table_file.close()

for filename in os.listdir(folder_path_type):
    # Read the name of the script file
    script_file_path = os.path.join(folder_path_type, filename)

    # Check if the script file exists
    if os.path.exists(script_file_path):
        # Read the content of the script file
        print('script_file_path: ',script_file_path)
        with open(script_file_path, 'r', encoding="utf8") as file:
            script_content = file.read()

        with open(merge_type_file_path, 'a', encoding="utf8") as merge_table_file:
            merge_content = '\n' + script_content
            merge_table_file.write(merge_content)
            merge_table_file.close()

with open(merge_type_file_path, 'a', encoding="utf8") as merge_table_file:
    ending = '\n' + 'COMMIT TRAN' + '\n'
    merge_table_file.write(ending)
    merge_table_file.close()


######### Merge partition
merge_partition_file_path = os.path.join(merge_path, rf"{DATABASE}_initial_partition_{date}.sql")
folder_path_partition_function = os.path.join(folder_path, r'Storage\Partition Functions')
folder_path_partition_schema = os.path.join(folder_path, r'Storage\Partition Schemes')

with open(merge_partition_file_path, 'w', encoding="utf8") as merge_table_file:
    beginning = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + '\n' + 'BEGIN TRAN' + '\n' + 'SET QUOTED_IDENTIFIER ON' + '\n' + 'GO' + '\n'
    merge_table_file.write(beginning)
    merge_table_file.close()

for filename in os.listdir(folder_path_partition_function):
    # Read the name of the script file
    script_file_path = os.path.join(folder_path_partition_function, filename)

    # Check if the script file exists
    if os.path.exists(script_file_path):
        # Read the content of the script file
        print('script_file_path: ',script_file_path)
        with open(script_file_path, 'r', encoding="utf8") as file:
            script_content = file.read()

        with open(merge_partition_file_path, 'a', encoding="utf8") as merge_table_file:
            merge_content = '\n' + script_content
            merge_table_file.write(merge_content)
            merge_table_file.close()

for filename in os.listdir(folder_path_partition_schema):
    # Read the name of the script file
    script_file_path = os.path.join(folder_path_partition_schema, filename)

    # Check if the script file exists
    if os.path.exists(script_file_path):
        # Read the content of the script file
        print('script_file_path: ',script_file_path)
        with open(script_file_path, 'r', encoding="utf8") as file:
            script_content = file.read()

        with open(merge_partition_file_path, 'a', encoding="utf8") as merge_table_file:
            merge_content = '\n' + script_content
            merge_table_file.write(merge_content)
            merge_table_file.close()

with open(merge_partition_file_path, 'a', encoding="utf8") as merge_table_file:
    ending = '\n' + 'COMMIT TRAN' + '\n'
    merge_table_file.write(ending)
    merge_table_file.close()


######### Merge SP
merge_sp_file_path = os.path.join(merge_path, rf"{DATABASE}_initial_sp_{date}.sql")
folder_path_sp = os.path.join(folder_path, r'Stored Procedures')

with open(merge_sp_file_path, 'w', encoding="utf8") as merge_table_file:
    beginning = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + '\n' + 'BEGIN TRAN' + '\n'
    merge_table_file.write(beginning)
    merge_table_file.close()

for filename in os.listdir(folder_path_sp):
    # Read the name of the script file
    script_file_path = os.path.join(folder_path_sp, filename)

    # Check if the script file exists
    if os.path.exists(script_file_path):
        # Read the content of the script file
        print('script_file_path: ',script_file_path)
        with open(script_file_path, 'r', encoding="utf8") as file:
            script_content = file.read()

        with open(merge_sp_file_path, 'a', encoding="utf8") as merge_table_file:
            merge_content = '\n' + script_content
            merge_table_file.write(merge_content)
            merge_table_file.close()

with open(merge_sp_file_path, 'a', encoding="utf8") as merge_table_file:
    ending = '\n' + 'COMMIT TRAN' + '\n'
    merge_table_file.write(ending)
    merge_table_file.close()


######### Merge Function
merge_function_file_path = os.path.join(merge_path, rf"{DATABASE}_initial_function_{date}.sql")
folder_path_function = os.path.join(folder_path, r'Functions')

if os.path.exists(folder_path_function):
    with open(merge_function_file_path, 'w', encoding="utf8") as merge_table_file:
        beginning = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + '\n' + 'BEGIN TRAN' + '\n'
        merge_table_file.write(beginning)
        merge_table_file.close()

    for filename in os.listdir(folder_path_function):
        # Read the name of the script file
        script_file_path = os.path.join(folder_path_function, filename)

        # Check if the script file exists
        if os.path.exists(script_file_path):
            # Read the content of the script file
            print('script_file_path: ',script_file_path)
            with open(script_file_path, 'r', encoding="utf8") as file:
                script_content = file.read()

            with open(merge_function_file_path, 'a', encoding="utf8") as merge_table_file:
                merge_content = '\n' + script_content
                merge_table_file.write(merge_content)
                merge_table_file.close()

    with open(merge_function_file_path, 'a', encoding="utf8") as merge_table_file:
        ending = '\n' + 'COMMIT TRAN' + '\n'
        merge_table_file.write(ending)
        merge_table_file.close()


######### Merge Schema
merge_schema_file_path = os.path.join(merge_path, rf"{DATABASE}_initial_schema_{date}.sql")
folder_path_schema = os.path.join(folder_path, r'Security\Schemas')

if os.path.exists(folder_path_schema):
    with open(merge_schema_file_path, 'w', encoding="utf8") as merge_table_file:
        beginning = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + '\n' + 'BEGIN TRAN' + '\n' + 'SET QUOTED_IDENTIFIER ON' + '\n' + 'GO' + '\n'
        merge_table_file.write(beginning)
        merge_table_file.close()

    for filename in os.listdir(folder_path_schema):
        # Read the name of the script file
        script_file_path = os.path.join(folder_path_schema, filename)

        # Check if the script file exists
        if os.path.exists(script_file_path):
            # Read the content of the script file
            print('script_file_path: ',script_file_path)
            with open(script_file_path, 'r', encoding="utf8") as file:
                script_content = file.read()

            with open(merge_schema_file_path, 'a', encoding="utf8") as merge_table_file:
                merge_content = '\n' + script_content
                merge_table_file.write(merge_content)
                merge_table_file.close()

    with open(merge_schema_file_path, 'a', encoding="utf8") as merge_table_file:
        ending = '\n' + 'COMMIT TRAN' + '\n'
        merge_table_file.write(ending)
        merge_table_file.close()

